/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package modele;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

/**
 *
 * @author Delphine
 */
public class User {

    private int id_user;
    private String nom;
    private String prenom;

    /**
     *
     * @param id_user
     * @param nom
     * @param prenom
     */
    public User(int id_user, String nom, String prenom) {
        this.id_user = id_user;
        this.nom = nom;
        this.prenom = prenom;
    }

    /**
     *
     * @return
     */
    public int getId_user() {
        return id_user;
    }

    /**
     *
     * @return
     */
    public String getNom() {
        return nom;
    }

    /**
     *
     * @return
     */
    public String getPrenom() {
        return prenom;
    }

    /**
     *
     * @return
     */
    public String getType(){
        if (this.getClass().equals(Etudiant.class))
            return "etudiant";
        else if (this.getClass().equals(Formateur.class))
            return "formateur";
        else
            return "administrateur";
    }


    /** Obtenir la liste des projets du User.
   *
     * @param id_user
     * @param type
     * @return
   * @throws java.sql.SQLException
   */
    public static ArrayList<Projet> getProjetsByUser(int id_user, String type) throws SQLException{
       ArrayList<Projet> projets = new ArrayList<Projet>();
       if (type.equals("administrateur"))
           return getAllProjets();
       else if (type.equals("formateur"))
           return Formateur.getProjets(id_user);
       else if (type.equals("etudiant"))
           return Etudiant.getProjets(id_user);
       else
           return null;  
    }

    /** Obtenir tous les projets. Null si pas trouv�.
   *
   * @return
   * @throws java.sql.SQLException
   */
    public static ArrayList<Projet> getAllProjets() throws SQLException{
        ArrayList<Projet> result = null;
        Connection connexion = null;
        Statement select = null;
        ResultSet rs = null;
        try {
          result = new ArrayList<Projet>();
          connexion = BD.connect();
          select = connexion.createStatement();
          String sql = "SELECT * FROM projet ORDER BY id_session";
          rs = select.executeQuery(sql);
          while (rs.next() != false) {
            result.add(new Projet(rs.getInt("id_projet"), rs.getString("nom"),(""+rs.getTimestamp("date_debut")).substring(0, 10), (""+rs.getTimestamp("date_fin")).substring(0, 10),Projet.getEquipesID(rs.getInt("id_projet")), Formateur.getById(rs.getInt("id_formateur")), Session_diplome.getById(rs.getInt("id_session"))));
          }
        }  finally {
            BD.close(rs);
            BD.close(select);
            BD.close(connexion);
        }
        return result;
    }

     /** Obtenir la liste des groupes du User si c'est un etudiant, null aussi non.
   *
      * @param id_user
      * @param type
      * @return
   * @throws java.sql.SQLException
   */
    public static ArrayList<Equipe> getEquipesByUser(int id_user, String type) throws SQLException{
       if (type.equals("etudiant"))
           return Etudiant.getEquipes(id_user);
       else
           return null;
    }

    /**
     *
     * @param id_user
     * @param type
     * @return
     * @throws SQLException
     */
    public static User getById(int id_user, String type) throws SQLException{
       if (type.equals("formateur"))
           return Formateur.getById(id_user);
       else if (type.equals("etudiant"))
           return Etudiant.getById(id_user);
       else
           return null;
    }

    /**
     *
     * @param id_user
     * @return
     * @throws SQLException
     */
    public static User getById(int id_user) throws SQLException{
        String query1 = "SELECT COUNT(*) FROM ETUDIANT_SESSION WHERE id_etudiant='" + id_user +"'";
        String query2 = "SELECT COUNT(*) FROM FORMATEUR WHERE id_formateur='" + id_user+"'";
        String query3 = "SELECT COUNT(*) FROM ADMINISTRATEUR WHERE identifiant='" + id_user +"'";
        ResultSet results;
        Connection c = BD.connect();
        try {
            Statement stmt = c.createStatement();
            results = stmt.executeQuery(query1);
            results.next();
            int interResult =  results.getInt(1);
            if (interResult > 0) {
                return Etudiant.getById(id_user);
            }
            else{
                results = stmt.executeQuery(query2);
                results.next();
                interResult =  results.getInt(1);
                if (interResult > 0){
                    return Formateur.getById(id_user);
                }
                else{
                    results = stmt.executeQuery(query3);
                    results.next();
                    interResult =  results.getInt(1);
                    if (interResult > 0) {
                        return new User(1, "admin", "admin");
                    }
                    else
                        return null;
                }
            }
        }
        catch(Exception e){
            System.out.println("pas de resultats");
            return null;
        }
    }

    /**
     *
     * @param name
     * @param type_user
     * @return
     * @throws SQLException
     */
    public static ArrayList<User> getByName(String name, String type_user) throws SQLException{
        String nom= name.toUpperCase();
        String query = null;
        if(type_user.equals("etudiant"))
            query = "SELECT E.id_etudiant FROM CANDIDAT C, ETUDIANT_SESSION E WHERE (UPPER(C.nom) LIKE '%" + nom +"%' OR UPPER(C.prenom) LIKE '%" + nom +"%') AND C.id_candidat = E.id_candidat";
        else if (type_user.equals("formateur"))
            query = "SELECT id_formateur FROM FORMATEUR WHERE (UPPER(nom) LIKE '%" +nom+"%' OR UPPER(prenom) LIKE '%" + nom+"%')";
        ResultSet results;
        Connection c = BD.connect();
        try {
            ArrayList<User> userList = new ArrayList<User>();
            Statement stmt = c.createStatement();
            if (type_user.equals("etudiant")) {
                results = stmt.executeQuery(query);
                while (results.next() != false) {
                    userList.add(Etudiant.getById(results.getInt("id_etudiant")));
                }
            } else if (type_user.equals("formateur")) {
                results = stmt.executeQuery(query);
                while (results.next() != false) {
                    userList.add(Formateur.getById(results.getInt("id_formateur")));
                }
            }
            if(userList.isEmpty())
                return null;
            return userList;
        }
        catch(Exception e){
            System.out.println("pas de resultats");
            return null;
        }
    }
}
